package com.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GradeDAO {

    /**
     * 获取指定学生的所有成绩。
     * @param studentUsername 学生的用户名（学号）
     * @return 该学生的所有成绩列表
     * @throws SQLException 如果发生数据库访问错误
     */
    public List<Grade> getGradesByStudentUsername(String studentUsername) throws SQLException {
        List<Grade> grades = new ArrayList<>();
        String sql = "SELECT courseName, score, semester, courseType, examTime, teacherName FROM grades WHERE studentUsername = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentUsername);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Grade grade = new Grade(
                    rs.getString("courseName"),
                    rs.getDouble("score"),
                    rs.getString("semester"),
                    rs.getString("courseType"),
                    studentUsername,
                    rs.getString("examTime"),
                    rs.getString("teacherName")
                );
                grades.add(grade);
            }
        }
        return grades;
    }

    /**
     * 获取指定学生按学期和课程类型筛选的成绩。
     * @param studentUsername 学生的用户名（学号）
     * @param semester 学期筛选条件 (null 或 "全部" 表示不筛选)
     * @param courseType 课程类型筛选条件 (null 或 "全部" 表示不筛选)
     * @return 筛选后的成绩列表
     * @throws SQLException 如果发生数据库访问错误
     */
    public List<Grade> getFilteredGradesForStudent(String studentUsername, String semester, String courseType) throws SQLException {
        List<Grade> grades = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT courseName, score, semester, courseType, examTime, teacherName FROM grades WHERE studentUsername = ?");
        List<Object> params = new ArrayList<>();
        params.add(studentUsername);

        if (semester != null && !semester.equalsIgnoreCase("全部")) {
            sql.append(" AND semester = ?");
            params.add(semester);
        }
        if (courseType != null && !courseType.equalsIgnoreCase("全部")) {
            sql.append(" AND courseType = ?");
            params.add(courseType);
        }

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Grade grade = new Grade(
                    rs.getString("courseName"),
                    rs.getDouble("score"),
                    rs.getString("semester"),
                    rs.getString("courseType"),
                    studentUsername,
                    rs.getString("examTime"),
                    rs.getString("teacherName")
                );
                grades.add(grade);
            }
        }
        return grades;
    }

    /**
     * 获取指定教师在特定课程和学期下的所有学生的成绩。
     * @param teacherUsername 教师的用户名
     * @param courseName 课程名称
     * @param semester 学期
     * @return 该教师在该课程和学期下的所有学生的成绩列表，包含学生姓名
     * @throws SQLException 如果发生数据库访问错误
     */
    public List<Grade> getGradesByTeacherCourseSemester(String teacherUsername, String courseName, String semester) throws SQLException {
        List<Grade> grades = new ArrayList<>();
        // 加入users表，通过studentUsername关联，获取学生的姓名
        String sql = "SELECT g.courseName, g.score, g.semester, g.courseType, g.studentUsername, g.examTime, g.teacherName, u.name as studentName " +
                     "FROM grades g " +
                     "JOIN users u ON g.studentUsername = u.username " +
                     "WHERE g.teacherName = ? AND g.courseName = ? AND g.semester = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, teacherUsername);
            pstmt.setString(2, courseName);
            pstmt.setString(3, semester);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Grade grade = new Grade(
                    rs.getString("courseName"),
                    rs.getDouble("score"),
                    rs.getString("semester"),
                    rs.getString("courseType"),
                    rs.getString("studentUsername"),
                    rs.getString("examTime"),
                    rs.getString("teacherName"),
                    rs.getString("studentName") // 使用新的构造函数，包含学生姓名
                );
                grades.add(grade);
            }
        }
        return grades;
    }

    /**
     * 更新学生的课程成绩。
     * @param grade 包含更新后成绩信息的Grade对象
     * @throws SQLException 如果发生数据库访问错误
     */
    public void updateStudentGrade(Grade grade) throws SQLException {
        String sql = "UPDATE grades SET score = ? WHERE studentUsername = ? AND courseName = ? AND semester = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setDouble(1, grade.getScore());
            pstmt.setString(2, grade.getStudentUsername());
            pstmt.setString(3, grade.getCourseName());
            pstmt.setString(4, grade.getSemester());

            pstmt.executeUpdate();
        }
    }

    /**
     * 根据多种条件查询学生成绩。
     * @param studentUsername 学生用户名（模糊查询）
     * @param studentName 学生姓名（模糊查询）
     * @param courseName 课程名称（模糊查询）
     * @param semester 学期（模糊查询）
     * @return 符合条件的成绩列表，包含学生姓名
     * @throws SQLException 如果发生数据库访问错误
     */
    public List<Grade> searchStudentGrades(String studentUsername, String studentName, String courseName, String semester) throws SQLException {
        List<Grade> grades = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT g.courseName, g.score, g.semester, g.courseType, g.studentUsername, g.examTime, g.teacherName, u.name as studentName ");
        sql.append("FROM grades g ");
        sql.append("JOIN users u ON g.studentUsername = u.username ");
        sql.append("WHERE 1=1 "); // Start with a true condition to easily append AND clauses

        List<String> params = new ArrayList<>();

        if (studentUsername != null && !studentUsername.isEmpty()) {
            sql.append(" AND g.studentUsername LIKE ?");
            params.add("%" + studentUsername + "%");
        }
        if (studentName != null && !studentName.isEmpty()) {
            sql.append(" AND u.name LIKE ?");
            params.add("%" + studentName + "%");
        }
        if (courseName != null && !courseName.isEmpty()) {
            sql.append(" AND g.courseName LIKE ?");
            params.add("%" + courseName + "%");
        }
        if (semester != null && !semester.isEmpty()) {
            sql.append(" AND g.semester LIKE ?");
            params.add("%" + semester + "%");
        }

        // Optionally add an ORDER BY clause
        sql.append(" ORDER BY g.semester DESC, g.courseName ASC, u.name ASC");

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                pstmt.setString(i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Grade grade = new Grade(
                    rs.getString("courseName"),
                    rs.getDouble("score"),
                    rs.getString("semester"),
                    rs.getString("courseType"),
                    rs.getString("studentUsername"),
                    rs.getString("examTime"),
                    rs.getString("teacherName"),
                    rs.getString("studentName") // Include student name
                );
                grades.add(grade);
            }
        }
        return grades;
    }

    /**
     * 插入新的学生课程成绩记录。
     * @param studentUsername 学生用户名
     * @param courseName 课程名称
     * @param score 成绩
     * @param semester 学期
     * @param courseType 课程类型
     * @param examTime 考试时间
     * @param teacherName 任课教师姓名
     * @throws SQLException 如果发生数据库访问错误
     */
    public void insertGrade(String studentUsername, String courseName, double score, String semester, String courseType, String examTime, String teacherName) throws SQLException {
        String sql = "INSERT INTO grades (studentUsername, courseName, score, semester, courseType, examTime, teacherName) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentUsername);
            pstmt.setString(2, courseName);
            pstmt.setDouble(3, score);
            pstmt.setString(4, semester);
            pstmt.setString(5, courseType);
            pstmt.setString(6, examTime);
            pstmt.setString(7, teacherName);
            pstmt.executeUpdate();
        }
    }

    // TODO: 添加其他可能的成绩相关DAO方法，如添加、修改、删除成绩等
} 